import pymysql
# coding=utf-8

from store.BaseStore import BaseStore
import time
import datetime

from util.date import *
from decimal import Decimal
from notify.item.HouseChangeItem import HouseChangeItem
from lib.utility.log import *


class HouseStore(BaseStore):

    def insert_house(self, house):
        select_sql = "select * from house_info where house_id=%s" % house.house_id
        cursor = self.execute(select_sql)
        result = cursor.fetchone()
        if not result or result is None:
            self.doInsert(house)
            #发送通知，写到
            return
        if self.change_price(house, result) or int(house.must_look) != int(result["must_look"]):
            #发通知
            house_chane = HouseChangeItem(HouseChangeItem.CHANGE_PIRCE, house, result["price"])
            # self.notify.notify_house_change(house_chane)
            #更新数据
            self.doUpdate(house, result,self.change_price(house, result))

    def doInsert(self, house):
        sql = """insert into house_info(
            `xuequ_id`,`xiaoqu_id`,`house_id`,`name`,`price`,`description`,`direction`,`online_day`,last_trade_day,bed_num,floor,`size`,arch_type,house_type,trade_detail,img,create_time,update_time,must_look,bed_real_num) 
             values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,%d)""" % (
            house.area, house.xiaoqu_id, house.house_id, house.name,  house.price, house.desc,house.direction,house.online_day,house.last_trade_day,
            house.bed_num,house.floor,house.size,house.arc_type,house.house_type,house.trade_detail,",".join(house.pic),datetime.datetime.now(),
            datetime.datetime.now(),house.must_look,self.deal_bed_number(house.bed_num)
        )
        logger.info(sql)
        cursor = self.execute(sql)
        print(cursor.rowcount)

    def doUpdate(self, house,result,change_price: bool):
        cur_count = result["change_count"]
        count = cur_count if not change_price else cur_count + 1
        sql = "update house_info set price='%s',update_time='%s',change_count=%d,must_look=%d where house_id='%s'" % (
        house.price, datetime.datetime.now(), count,int(house.must_look),house.house_id)
        cursor = self.execute(sql)
        if change_price:
            sql = "insert into house_change_detail(`xuequ_id`,`xiaoqu_id`,`house_id`,change_day,qichu_price,qimo_price,create_time) values('%s','%s','%s','%s','%s','%s','%s')" % (
                house.area, house.xiaoqu_id, house.house_id, get_date_string(), result["price"], house.price,
                datetime.datetime.now()
            )
            cursor = self.execute(sql)
            return cursor.rowcount

    #价格是否发生变化
    def change_price(self,house,db_data) -> bool:
        return round(Decimal(house.price) - Decimal(db_data["price"]), 0) != 0

    def has_offline(self,house,db_data) -> bool:
        return False

    def set_like(self,house_id,is_like:int):
        is_like = 1 if is_like else 0
        sql = "update house_info set `like`=%d,update_time='%s' where house_id='%s'" % (is_like,datetime.datetime.now(),house_id)
        logger.info(sql)
        self.execute(sql)

    def set_status(self,house_id,status:int):
        if status != 0 and status != 1:
            raise Exception("status只能是0 或者1")
        sql = "update house_info set status=%d,update_time='%s' where house_id='%s'" % (status, datetime.datetime.now(), house_id)
        self.execute(sql)

    def get_house_change_detail(self,house_id,house_info=None):
        if house_info is None:
            select_sql = "select * from house_info where house_id=%s" % house_id
            cursor = self.execute(select_sql)
            house_info = cursor.fetchone()
            if not house_info or house_info is None:
                raise Exception("未查到房子数据")
        select_detail_sql = "select * from house_change_detail where house_id=%s" % house_id
        cursor = self.execute(select_detail_sql)
        result = cursor.fetchall()
        detail_list = result if not result or result is None else []
        return house_info, detail_list

    def get_all_like_house(self):
        select_sql = "select * from house_info where `like`=1"
        cursor = self.execute(select_sql)
        return cursor.fetchall()

    def get_all_must_look_house(self):
        select_sql = "select * from house_info where `must_look`=1"
        cursor = self.execute(select_sql)
        return cursor.fetchall()

    def get_house_number(self):
        select_sql = "select count(*) as total from house_info where status=0"
        cursor = self.execute(select_sql)
        return cursor.fetchone()["total"]

    def get_by_house_id(self,house_id):
        sql = "select * from house_info where house_id='%s'" % (house_id)
        cursor = self.execute(sql)
        return cursor.fetchone()


    def get_target_house(self,district,min_price,max_price,size,max_size,xiaoqu_id_list=[],must_look=None):
        sql = '''
         select h.name,c.name,price,size,floor,online_day,last_trade_day,h.xuequ_id,h.house_id,h.xiaoqu_id  from house_info h join xiaoqu_info c
          on h.xiaoqu_id=c.xiaoqu_id where price>={0} 
         and price <={1} and size>={2}  and size <= {3} and floor not like "%顶层%"\
    and floor not like "%地下室%" and floor not like "%底层%" and status=0  
         '''.format (min_price,max_price,size,max_size)
        if district is not None and district:
            sql = sql + " and c.district='{0}'".format(district)
        if must_look is not None and must_look:
            if int(must_look) == 1:
                sql = sql + " and h.must_look='{0}'".format(must_look)
        if len(xiaoqu_id_list) > 0:
            xiaoqu_sql = " and c.xiaoqu_id in (" + ",".join(["'%s'" for i in range(len(xiaoqu_id_list))]) + ")"
            param = []
            [param.append(i) for i in xiaoqu_id_list]
            sql += xiaoqu_sql % tuple(param)
        logger.info(sql)
        cursor = self.execute(sql)
        return cursor.fetchall()

    def deal_bed_number(self, bed_num):
        if "室" in bed_num:
            bed_num = bed_num.split("室")
        elif "房间" in bed_num:
            bed_num = bed_num.split("房间")
        else:
            return 0
        bed_num = bed_num[0]
        return int(bed_num.strip())

    def flush_bed_num(self, house):
        sql = "update house_info set `bed_real_num`=%d,update_time='%s' where house_id='%s'" % (
        self.deal_bed_number(house["bed_num"]), datetime.datetime.now(), house["house_id"])
        logger.info("房屋卧室数量刷新完毕:{0}".format(house["house_id"]))
        self.execute(sql)

    def get_all(self):
        select_sql = "select * from house_info"
        cursor = self.execute(select_sql)
        return cursor.fetchall()

    def flush_all_bed(self):
        data = self.get_all()
        [self.flush_bed_num(x) for x in data]

    def get_day_house(self,day):
        start_day = day + "00:00:00"
        end_dat = start_day
        select_sql = "select * from house_info where create_time >= '%s' and create_time<'%s' " % (start_day,end_dat)
        pass


if __name__ == "__main__":
    s = HouseStore()
    a = "5房间1卫"
    print(s.deal_bed_number(a))
    s.flush_all_bed()
